'''
Author: cw
Date: 2025-07-22
Description: 从excel中导入题目
'''
import re
import sys
sys.path.append('./')

import pandas as pd

from models.mysql_model import Questions
from models.data_model import OptionalModel
from utils import db_util


async def get_data_from_excel(file_path: str, sheet_name: str):
    # 读取excel文件
    df = pd.read_excel(file_path, sheet_name)
    data = df.to_dict(orient='records')
    return data


def parse_options(str_options: str):
    if not str_options:
        raise ValueError('选项不能为空')
    options = str_options.split('\n')
    result = []
    for option in options:
        # 分数
        if '#' in option:
            score = option.split('#')[1].strip().replace('\u200b', '')
            option = option.split('#')[0].strip().replace('\u200b', '')
        else:
            score = 0
        # 选项 & 内容
        if not option:
            continue
        pattern = r'^([A-Za-z])\.?\s*(.*?)(?=[A-Za-z]\.|$)'
        match = re.match(pattern, option)
        result.append(OptionalModel(**{
            'key': match.group(1),
            'value': match.group(2).strip().replace('\n', '').replace('\u200b', ''),
            'score': int(score)
        }).model_dump())
    return result

async def run(test_id: int):
    data = await get_data_from_excel('./tools/公众号题库-0724.xlsx', sheet_name=str(test_id + 1))
    questions = []
    total = len(data)
    s = 0
    for item in data:
        s += 1
        print(f'==============={s}/{total}')
        options = parse_options(item.pop('options', None))
        question = Questions(
            test_id=int(test_id),
            group=item.get('group'),
            question_title=item.get('question_title'),
            options=options,
            question_type = "single",
            checked=item.get('checked'),
        )
        questions.append(question)
    if not questions:
        raise ValueError('题目不能为空')

    async with db_util.session_context() as session:
        session.add_all(questions)


if __name__ == '__main__':
    import asyncio
    asyncio.run(run(10))